package df.bean.process;

import df.bean.db.conn.DBConnection;
import df.bean.db.table.Batch;
import df.bean.db.table.TRN_Error;
import df.bean.db.table.TrnDaily;
import df.bean.obj.util.JDate;


public class ProcessImport {
    private DBConnection conn = null;
    
    
    public ProcessImport(DBConnection conn) {
        this.conn = conn;
    }
    // TrnDaily
    public boolean importBill(String hospitalCode, String startDate, String endDate) {
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE "
           // add for vtn
           + ",FIRST_LOCATION_CODE "
           + ",FIRST_DEPARTMENT_CODE "
           + ",PRIVATE_DOCTOR "
           + ",EPISODE_TYPE "
           + ",TEMP_LOCATION_CODE "
           + ",TEMP_LOCATION_DESCRIPTION "
           + ",TEMP_DEPARTMENT_CODE "
           + ",TEMP_DEPARTMENT_DESCRIPTION "
           + ",DERIVED_FEE_RULE "
           + ",NOTE "
           + ")"
     + " SELECT HOSPITAL_CODE" + ",BILL_NO" + ", BILL_DATE" + ", '', ''"
           + ", TRANSACTION_DATE" + ", HN_NO" + ", PATIENT_NAME" + ", EPISODE_NO" 
           + ", PAYOR_CODE" + ", PAYOR_NAME" + ", '" + TrnDaily.TRANSACTION_MODULE_TR + "'"
           + ", TRANSACTION_TYPE"
           + ", PAYOR_CATEGORY_CODE" + ", PAYOR_CATEGORY_DESC, 'N' "
           + ", LINE_NO" 
           + ", ADMISSION_TYPE_CODE"
           + ", NATIONALITY_CODE, NATIONALITY_DESCRIPTION"
           + ", PATIENT_LOCATION_DEPT_CODE" + ", PATIENT_LOCATION_CODE"
           + ", RECEIVING_LOCATION_DEPT_CODE" + ", RECEIVING_LOCATION_CODE" 
           + ", '', ORDER_ITEM_CODE" 
           + ", ORDER_ITEM_DESCRIPTION" + ", DOCTOR_CODE" + ", VERIFIED_DATE" 
           + ", VERIFIED_TIME"  + ", DOCTOR_CODE" 
           + ", ORDERED_DATE, ORDERED_TIME, '', DOCTOR_CODE" 
           + ", RECEIPT_TYPE_CODE" + ", AMOUNT_BEF_DISCOUNT" 
           + ", AMOUNT_OF_DISCOUNT" + ", AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT, AMOUNT_BEF_DISCOUNT" 
           + ", 1, 1, " 
           + "'" + JDate.getDate() +"','" + JDate.getTime() + "'" + ",'" + this.conn.getUserID() + "'"
           + ",'', '','','',0,0,0,0,0,0,0,'',0,0,'','','','','','','',0,0,0,'','','','','',0,0,'1',INVOICE_TYPE " 
           + ",BILL_TOTAL_AMOUNT,0,AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT"
           + ",'N','N','N','N','N',0, ''"
           // add for vtn
           + ",FIRST_LOCATION_CODE "
           + ",FIRST_DEPARTMENT_CODE "
           + ",'N' " //PRIVATE_DOCTOR
           + ",EPISODE_TYPE "
           + ",TEMP_LOCATION_CODE "
           + ",TEMP_LOCATION_DESCRIPTION "
           + ",TEMP_DEPARTMENT_CODE "
           + ",TEMP_DEPARTMENT_DESCRIPTION "
           + ",DERIVED_FEE_RULE "
           + ",NOTE "
           // end of add for vtn
           + " FROM INT_HIS_BILL " +
             " WHERE (TRANSACTION_DATE >= '" + startDate + "'" +
             " AND HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND AMOUNT_BEF_DISCOUNT >= 0"+
             " AND TRANSACTION_DATE <= '" + endDate + "')";
           
        try {
//          conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("Import Bill : " + sql);
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import data error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }
    // TrnPatho
    public boolean importPatho(String hospitalCode, String startDate, String endDate) {
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE " //LINE 29
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE )"
     + " SELECT HOSPITAL_CODE" + ",BILL_NO" + ", BILL_DATE" + ", '', ''"
           + ", TRANSACTION_DATE" + ", HN_NO" + ", PATIENT_NAME" + ", EPISODE_NO" 
           + ", PAYOR_CODE" + ", PAYOR_NAME" + ", '" + TrnDaily.TRANSACTION_MODULE_TR + "'"
           + ", TRANSACTION_TYPE"
           + ", PAYOR_CATEGORY_CODE" + ", PAYOR_CATEGORY_DESC, 'N' "
           + ", LINE_NO" 
           + ", ADMISSION_TYPE_CODE"
           + ", NATIONALITY_CODE, NATIONALITY_DESCRIPTION"
           + ", PATIENT_LOCATION_DEPT_CODE" + ", PATIENT_LOCATION_CODE"
           + ", RECEIVING_LOCATION_DEPT_CODE" + ", RECEIVING_LOCATION_CODE" 
           + ", '', ORDER_ITEM_CODE" 
           + ", ORDER_ITEM_DESCRIPTION" + ", DOCTOR_CODE" + ", VERIFIED_DATE" 
           + ", VERIFIED_TIME"  + ", DOCTOR_CODE" 
           + ", ORDERED_DATE, ORDERED_TIME, '', DOCTOR_CODE" 
           + ", RECEIPT_TYPE_CODE" + ", AMOUNT_BEF_DISCOUNT" 
           + ", AMOUNT_OF_DISCOUNT" + ", AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT, AMOUNT_BEF_DISCOUNT" 
           + ", 1, 1, " 
           + "'" + JDate.getDate() +"','" + JDate.getTime() + "'" + ",'" + this.conn.getUserID() + "'"
           + ",'', '','','',0,0,0,0,0,0,0,'',0,0,'','','','','','','',0,0,0,'','','','','',0,0,'1',INVOICE_TYPE " 
           + ",BILL_TOTAL_AMOUNT,0,AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT"
           + ",'N','N','N','N','N',0, ''"
           + " FROM INT_HIS_BILL " +
             " WHERE (TRANSACTION_DATE >= '" + startDate + "'" +
             " AND HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND AMOUNT_BEF_DISCOUNT <> 0 AND RECEIVING_LOCATION_CODE = '01PAT'"+
             " AND TRANSACTION_DATE <= '" + endDate + "')";
           
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
//            DialogBox.ShowError("Error : " + ex.getMessage());
            System.out.println("Import Bill : " + sql);
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import data error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }

    // Verify
    public boolean importVerifyInMonth(String hospitalCode, String startDate, String endDate) {
    	Batch batch = new Batch(hospitalCode, conn);
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR " 
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE " 
           // add for vtn
           + ",FIRST_LOCATION_CODE "
           + ",FIRST_DEPARTMENT_CODE "
           + ",PRIVATE_DOCTOR "
           + ",EPISODE_TYPE "
           + ",TEMP_LOCATION_CODE "
           + ",TEMP_LOCATION_DESCRIPTION "
           + ",TEMP_DEPARTMENT_CODE "
           + ",TEMP_DEPARTMENT_DESCRIPTION "
           + ",DERIVED_FEE_RULE "
           + ",NOTE "
           // end
           + ")"
     + " SELECT T.HOSPITAL_CODE "
           + ",T.INVOICE_NO "
           + ",T.INVOICE_DATE "
           + ",T.RECEIPT_NO "
           + ",T.RECEIPT_DATE "
           + ",V.TRANSACTION_DATE "
           + ",T.HN_NO "
           + ",T.PATIENT_NAME "
           + ",T.EPISODE_NO "
           + ",T.PAYOR_OFFICE_CODE "
           + ",T.PAYOR_OFFICE_NAME "
           + ",T.TRANSACTION_MODULE "
           + ",T.TRANSACTION_TYPE "
           + ",T.PAYOR_OFFICE_CATEGORY_CODE "
           + ",T.PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",T.IS_WRITE_OFF "
           + ",T.LINE_NO+'V' "
           + ",T.ADMISSION_TYPE_CODE "
           + ",T.NATIONALITY_CODE "
           + ",T.NATIONALITY_DESCRIPTION "
           + ",T.PATIENT_DEPARTMENT_CODE "
           + ",T.PATIENT_LOCATION_CODE "
           + ",T.RECEIPT_DEPARTMENT_CODE "
           + ",T.RECEIPT_LOCATION_CODE "
           + ",D.DEPARTMENT_CODE "   // DOCTOR_DEPARTMENT_CODE ดูว่าใช้ department อะไร
           + ",T.ORDER_ITEM_CODE "
           + ",T.ORDER_ITEM_DESCRIPTION "
           + ",V.DOCTOR_CODE "
           + ",V.VERIFIED_DATE "
           + ",V.VERIFIED_TIME "
           + ",T.DOCTOR_EXECUTE_CODE "
           + ",T.EXECUTE_DATE "
           + ",T.EXECUTE_TIME "
           + ",V.DOCTOR_CODE "
           + ",V.DOCTOR_CODE "
           + ",T.RECEIPT_TYPE_CODE "
           + ",T.AMOUNT_BEF_DISCOUNT "
           + ",T.AMOUNT_OF_DISCOUNT "
           + ",T.AMOUNT_AFT_DISCOUNT "
           + ",T.AMOUNT_BEF_WRITE_OFF "
           + ",T.INV_IS_VOID "
           + ",T.REC_IS_VOID "
           + ",'" + JDate.getDate() + "'"
           + ",'" + JDate.getTime() + "'"
           + ",'" + this.conn.getUserID() + "'"
           + ",'' " // Batch No
           + ",T.YYYY "
           + ",T.MM "
           + ",T.DD "
           + ",0" //T.NOR_ALLOCATE_AMT 
           + ",0" //T.NOR_ALLOCATE_PCT "
           + ",0" //T.DR_AMT "
           + ",0" //T.DR_TAX_400 "
           + ",0" //T.DR_TAX_401 "
           + ",0" //T.DR_TAX_402 "
           + ",0" //T.DR_TAX_406 "
           + ",''" //T.TAX_TYPE_CODE "
           + ",0" //T.DR_PREMIUM "
           + ",0" //T.GUARANTEE_AMT "
           + ",''" //T.GUARANTEE_CODE "
           + ",''" //T.GUARANTEE_DR_CODE "
           + ",''" //T.GUARANTEE_TYPE "
           + ",''" //T.GUARANTEE_DATE_TIME "
           + ",''" //T.GUARANTEE_TERM_MM "
           + ",''" //T.GUARANTEE_TERM_YYYY "
           + ",''" //T.IS_GUARANTEE "
           + ",0" //T.HP_AMT "
           + ",0" //T.HP_PREMIUM "
           + ",0" //T.HP_TAX "
           + ",''" //T.COMPUTE_DAILY_DATE "
           + ",''" //T.COMPUTE_DAILY_TIME "
           + ",''" //T.COMPUTE_DAILY_USER_ID "
           + ",''" //T.DOCTOR_CATEGORY_CODE "
           + ",''" //T.EXCLUDE_TREATMENT "
           + ",0" //T.PREMIUM_CHARGE_PCT "
           + ",0" //T.PREMIUM_REC_AMT "
           + ",T.ACTIVE "
           + ",'" + TrnDaily.INVOICE_TYPE_RESULT + "'"  // T.INVOICE_TYPE
           + ",T.TOTAL_BILL_AMOUNT "
           + ",T.TOTAL_DR_REC_AMOUNT "
           + ",T.OLD_AMOUNT "
           + ",T.PAY_BY_CASH "
           + ",T.PAY_BY_AR "
           + ",T.PAY_BY_DOCTOR "
           + ",T.PAY_BY_PAYOR "
           + ",T.PAY_BY_CASH_AR "
           + ",0 "  // T.OLD_DR_AMT
           + ",T.ORDER_ITEM_CATEGORY_CODE "
           // add for vtn
           + ",T.FIRST_LOCATION_CODE "
           + ",T.FIRST_DEPARTMENT_CODE "
           + ",'N' " //T.PRIVATE_DOCTOR 
           + ",T.EPISODE_TYPE "
           + ",T.TEMP_LOCATION_CODE "
           + ",T.TEMP_LOCATION_DESCRIPTION "
           + ",T.TEMP_DEPARTMENT_CODE "
           + ",T.TEMP_DEPARTMENT_DESCRIPTION "
           + ",'VERIFY' "//+ ",T.DERIVED_FEE_RULE "
           + ",T.NOTE "
           // end           
           + " FROM INT_HIS_VERIFY V INNER JOIN TRN_DAILY T ON V.HOSPITAL_CODE = T.HOSPITAL_CODE " 
           //+ " AND V.LINE_NO = T.LINE_NO"
           + " AND V.BILL_NO = T.INVOICE_NO AND V.LINE_NO = T.LINE_NO"
           + " LEFT JOIN DOCTOR D ON V.DOCTOR_CODE = D.CODE AND V.HOSPITAL_CODE = D.HOSPITAL_CODE "
           + " WHERE (V.TRANSACTION_DATE >= '" + startDate + "'"
           + " AND V.TRANSACTION_DATE <= '" + endDate + "')"
           + " AND (T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_ORDER + "'"
           + " OR T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_PAY_ORDER + "')"
           + " AND V.HOSPITAL_CODE = '" + hospitalCode + "'" 
           + " AND T.INVOICE_DATE >= '" + batch.getYyyy() + batch.getMm() + "01'"
           + " AND T.INVOICE_DATE <= '" + batch.getYyyy() + batch.getMm() + "31'";
        
           
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
//            DialogBox.ShowError("Error : " + ex.getMessage());
            System.out.println("importVerifyInMonth" + ex.getMessage());
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import verify data is fails", ex.getMessage(), sql);
        } finally {
        	batch=null;
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    
    // Verify
    public boolean importVerifyRecOverMonth(String hospitalCode, String startDate, String endDate) {
    	Batch batch = new Batch(hospitalCode, conn);
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE "
           // add for vtn
           + ",FIRST_LOCATION_CODE "
           + ",FIRST_DEPARTMENT_CODE "
           + ",PRIVATE_DOCTOR "
           + ",EPISODE_TYPE "
           + ",TEMP_LOCATION_CODE "
           + ",TEMP_LOCATION_DESCRIPTION "
           + ",TEMP_DEPARTMENT_CODE "
           + ",TEMP_DEPARTMENT_DESCRIPTION "
           + ",DERIVED_FEE_RULE "
           + ",NOTE "
           // end           
           + ")"
     + " SELECT T.HOSPITAL_CODE "
           + ",T.INVOICE_NO "
           + ",V.TRANSACTION_DATE " // T.INVOICE_DATE // DIFF
           + ",T.RECEIPT_NO "
           + ",T.RECEIPT_DATE "
           + ",V.TRANSACTION_DATE "
           + ",T.HN_NO "
           + ",T.PATIENT_NAME "
           + ",T.EPISODE_NO "
           + ",T.PAYOR_OFFICE_CODE "
           + ",T.PAYOR_OFFICE_NAME "
           + ",T.TRANSACTION_MODULE "
           + ",T.TRANSACTION_TYPE "
           + ",T.PAYOR_OFFICE_CATEGORY_CODE "
           + ",T.PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",T.IS_WRITE_OFF "
           + ",T.LINE_NO+'V' "
           + ",T.ADMISSION_TYPE_CODE "
           + ",T.NATIONALITY_CODE "
           + ",T.NATIONALITY_DESCRIPTION "
           + ",T.PATIENT_DEPARTMENT_CODE "
           + ",T.PATIENT_LOCATION_CODE "
           + ",T.RECEIPT_DEPARTMENT_CODE "
           + ",T.RECEIPT_LOCATION_CODE "
           + ",D.DEPARTMENT_CODE "   // DOCTOR_DEPARTMENT_CODE ดูว่าใช้ department อะไร
           + ",T.ORDER_ITEM_CODE "
           + ",T.ORDER_ITEM_DESCRIPTION "
           + ",V.DOCTOR_CODE "
           + ",V.VERIFIED_DATE "
           + ",V.VERIFIED_TIME "
           + ",T.DOCTOR_EXECUTE_CODE "
           + ",T.EXECUTE_DATE "
           + ",T.EXECUTE_TIME "
           + ",V.DOCTOR_CODE "
           + ",V.DOCTOR_CODE "
           + ",T.RECEIPT_TYPE_CODE "
           + ",T.AMOUNT_BEF_DISCOUNT "
           + ",T.AMOUNT_OF_DISCOUNT "
           + ",T.AMOUNT_AFT_DISCOUNT "
           + ",T.AMOUNT_BEF_WRITE_OFF "
           + ",T.INV_IS_VOID "
           + ",T.REC_IS_VOID "
           + ",'" + JDate.getDate() + "'"
           + ",'" + JDate.getTime() + "'"
           + ",'" + this.conn.getUserID() + "'"
           + ",'' " // Batch No
           + ",'" + batch.getYyyy() + "'" // T.YYYY // diff
           + ",'" + batch.getMm() + "'" //T.MM // diff
           + ",T.DD "   // not sure
           + ",0" //T.NOR_ALLOCATE_AMT 
           + ",0" //T.NOR_ALLOCATE_PCT "
           + ",0" //T.DR_AMT "
           + ",0" //T.DR_TAX_400 "
           + ",0" //T.DR_TAX_401 "
           + ",0" //T.DR_TAX_402 "
           + ",0" //T.DR_TAX_406 "
           + ",''" //T.TAX_TYPE_CODE "
           + ",0" //T.DR_PREMIUM "
           + ",0" //T.GUARANTEE_AMT "
           + ",''" //T.GUARANTEE_CODE "
           + ",''" //T.GUARANTEE_DR_CODE "
           + ",''" //T.GUARANTEE_TYPE "
           + ",''" //T.GUARANTEE_DATE_TIME "
           + ",''" //T.GUARANTEE_TERM_MM "
           + ",''" //T.GUARANTEE_TERM_YYYY "
           + ",''" //T.IS_GUARANTEE "
           + ",0" //T.HP_AMT "
           + ",0" //T.HP_PREMIUM "
           + ",0" //T.HP_TAX "
           + ",''" //T.COMPUTE_DAILY_DATE "
           + ",''" //T.COMPUTE_DAILY_TIME "
           + ",''" //T.COMPUTE_DAILY_USER_ID "
           + ",''" //T.DOCTOR_CATEGORY_CODE "
           + ",''" //T.EXCLUDE_TREATMENT "
           + ",0" //T.PREMIUM_CHARGE_PCT "
           + ",0" //T.PREMIUM_REC_AMT "
           + ",T.ACTIVE "
           + ",'" + TrnDaily.INVOICE_TYPE_RESULT + "'"  // T.INVOICE_TYPE
           + ",T.TOTAL_BILL_AMOUNT "
           + ",T.TOTAL_DR_REC_AMOUNT "
           + ",T.OLD_AMOUNT "
           + ",T.PAY_BY_CASH "
           + ",T.PAY_BY_AR "
           + ",T.PAY_BY_DOCTOR "
           + ",T.PAY_BY_PAYOR "
           + ",T.PAY_BY_CASH_AR "
           + ",0 " //T.OLD_DR_AMT
           + ",T.ORDER_ITEM_CATEGORY_CODE "
           // add for vtn
           + ",T.FIRST_LOCATION_CODE "
           + ",T.FIRST_DEPARTMENT_CODE "
           + ",'N' " //T.PRIVATE_DOCTOR 
           + ",T.EPISODE_TYPE "
           + ",T.TEMP_LOCATION_CODE "
           + ",T.TEMP_LOCATION_DESCRIPTION "
           + ",T.TEMP_DEPARTMENT_CODE "
           + ",T.TEMP_DEPARTMENT_DESCRIPTION "
           + ",'VERIFY' "//+ ",T.DERIVED_FEE_RULE "
           + ",T.NOTE "
           // end
           + " FROM INT_HIS_VERIFY V INNER JOIN TRN_DAILY T ON V.HOSPITAL_CODE = T.HOSPITAL_CODE " 
           //+ " AND V.LINE_NO = T.LINE_NO"
           + " AND V.BILL_NO = T.INVOICE_NO AND V.LINE_NO = T.LINE_NO"
           + " LEFT JOIN DOCTOR D ON V.DOCTOR_CODE = D.CODE AND V.HOSPITAL_CODE = D.HOSPITAL_CODE "
           + " WHERE (V.TRANSACTION_DATE >= '" + startDate + "'"
           + " AND V.TRANSACTION_DATE <= '" + endDate + "')"
           + " AND (T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_ORDER + "'"
           + " OR T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_PAY_ORDER + "')"
           + " AND V.HOSPITAL_CODE = '" + hospitalCode + "'" 
           + " AND T.INVOICE_DATE < '" + batch.getYyyy() + batch.getMm() + "00'" // difference
           + " AND (T.PAY_BY_CASH = 'Y' OR T.PAY_BY_AR = 'Y' OR T.PAY_BY_DOCTOR = 'Y' "
           + " OR T.PAY_BY_PAYOR = 'Y' OR T.PAY_BY_CASH_AR = 'Y')";
           
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("importVerifyRecOverMonth" + ex.getMessage());
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import verify data is fails", ex.getMessage(), sql);
            //            DialogBox.ShowError("Error : " + ex.getMessage());
        } finally {
        	batch = null;
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    
    // Verify
    public boolean importVerifyNotRec(String hospitalCode, String startDate, String endDate) {
    	Batch batch = new Batch(hospitalCode, conn);
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT" 
           // add for vtn
           + ",FIRST_LOCATION_CODE "
           + ",FIRST_DEPARTMENT_CODE "
           + ",PRIVATE_DOCTOR "
           + ",EPISODE_TYPE "
           + ",TEMP_LOCATION_CODE "
           + ",TEMP_LOCATION_DESCRIPTION "
           + ",TEMP_DEPARTMENT_CODE "
           + ",TEMP_DEPARTMENT_DESCRIPTION "
           + ",DERIVED_FEE_RULE "
           + ",NOTE "
           // end           
           + ") "
     + " SELECT T.HOSPITAL_CODE "
           + ",T.INVOICE_NO "
           + ",V.TRANSACTION_DATE " // T.INVOICE_DATE // DIFF
           + ",T.RECEIPT_NO "
           + ",T.RECEIPT_DATE "
           + ",V.TRANSACTION_DATE "
           + ",T.HN_NO "
           + ",T.PATIENT_NAME "
           + ",T.EPISODE_NO "
           + ",T.PAYOR_OFFICE_CODE "
           + ",T.PAYOR_OFFICE_NAME "
           + ",T.TRANSACTION_MODULE "
           + ",T.TRANSACTION_TYPE "
           + ",T.PAYOR_OFFICE_CATEGORY_CODE "
           + ",T.PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",T.IS_WRITE_OFF "
           + ",T.LINE_NO+'V' "
           + ",T.ADMISSION_TYPE_CODE "
           + ",T.NATIONALITY_CODE "
           + ",T.NATIONALITY_DESCRIPTION "
           + ",T.PATIENT_DEPARTMENT_CODE "
           + ",T.PATIENT_LOCATION_CODE "
           + ",T.RECEIPT_DEPARTMENT_CODE "
           + ",T.RECEIPT_LOCATION_CODE "
           + ",D.DEPARTMENT_CODE "   // DOCTOR_DEPARTMENT_CODE ดูว่าใช้ department อะไร
           + ",T.ORDER_ITEM_CODE "
           + ",T.ORDER_ITEM_DESCRIPTION "
           + ",V.DOCTOR_CODE "
           + ",V.VERIFIED_DATE "
           + ",V.VERIFIED_TIME "
           + ",T.DOCTOR_EXECUTE_CODE "
           + ",T.EXECUTE_DATE "
           + ",T.EXECUTE_TIME "
           + ",V.DOCTOR_CODE "
           + ",V.DOCTOR_CODE "
           + ",T.RECEIPT_TYPE_CODE "
           + ",T.AMOUNT_BEF_DISCOUNT "
           + ",T.AMOUNT_OF_DISCOUNT "
           + ",T.AMOUNT_AFT_DISCOUNT "
           + ",T.AMOUNT_BEF_WRITE_OFF "
           + ",T.INV_IS_VOID "
           + ",T.REC_IS_VOID "
           + ",'" + JDate.getDate() + "'"
           + ",'" + JDate.getTime() + "'"
           + ",'" + this.conn.getUserID() + "'"
           + ",'' " // Batch No
           + ", T.YYYY "
           + ", T.MM "
           + ",T.DD "
           + ",0" //T.NOR_ALLOCATE_AMT 
           + ",0" //T.NOR_ALLOCATE_PCT "
           + ",0" //T.DR_AMT "
           + ",0" //T.DR_TAX_400 "
           + ",0" //T.DR_TAX_401 "
           + ",0" //T.DR_TAX_402 "
           + ",0" //T.DR_TAX_406 "
           + ",''" //T.TAX_TYPE_CODE "
           + ",0" //T.DR_PREMIUM "
           + ",0" //T.GUARANTEE_AMT "
           + ",''" //T.GUARANTEE_CODE "
           + ",''" //T.GUARANTEE_DR_CODE "
           + ",''" //T.GUARANTEE_TYPE "
           + ",''" //T.GUARANTEE_DATE_TIME "
           + ",''" //T.GUARANTEE_TERM_MM "
           + ",''" //T.GUARANTEE_TERM_YYYY "
           + ",''" //T.IS_GUARANTEE "
           + ",0" //T.HP_AMT "
           + ",0" //T.HP_PREMIUM "
           + ",0" //T.HP_TAX "
           + ",''" //T.COMPUTE_DAILY_DATE "
           + ",''" //T.COMPUTE_DAILY_TIME "
           + ",''" //T.COMPUTE_DAILY_USER_ID "
           + ",''" //T.DOCTOR_CATEGORY_CODE "
           + ",''" //T.EXCLUDE_TREATMENT "
           + ",0" //T.PREMIUM_CHARGE_PCT "
           + ",0" //T.PREMIUM_REC_AMT "
           + ",T.ACTIVE "
           + ",'" + TrnDaily.INVOICE_TYPE_RESULT + "'"  // T.INVOICE_TYPE
           + ",T.TOTAL_BILL_AMOUNT "
           + ",T.TOTAL_DR_REC_AMOUNT "
           + ",T.OLD_AMOUNT "
           + ",T.PAY_BY_CASH "
           + ",T.PAY_BY_AR "
           + ",T.PAY_BY_DOCTOR "
           + ",T.PAY_BY_PAYOR "
           + ",T.PAY_BY_CASH_AR"
           + ",0 "  // T.OLD_DR_AMT
           // add for vtn
           + ",T.FIRST_LOCATION_CODE "
           + ",T.FIRST_DEPARTMENT_CODE "
           + ",'N' " //T.PRIVATE_DOCTOR 
           + ",T.EPISODE_TYPE "
           + ",T.TEMP_LOCATION_CODE "
           + ",T.TEMP_LOCATION_DESCRIPTION "
           + ",T.TEMP_DEPARTMENT_CODE "
           + ",T.TEMP_DEPARTMENT_DESCRIPTION "
           + ",'VERIFY' "//+ ",T.DERIVED_FEE_RULE "
           + ",T.NOTE "
           // end
           + " FROM INT_HIS_VERIFY V INNER JOIN TRN_DAILY T ON V.HOSPITAL_CODE = T.HOSPITAL_CODE " 
           //+ " AND V.LINE_NO = T.LINE_NO"
           + " AND V.BILL_NO = T.INVOICE_NO AND V.LINE_NO = T.LINE_NO"
           + " LEFT JOIN DOCTOR D ON V.DOCTOR_CODE = D.CODE AND V.HOSPITAL_CODE = D.HOSPITAL_CODE "
           + " WHERE V.TRANSACTION_DATE >= '" + startDate + "'"
           + " AND V.TRANSACTION_DATE <= '" + endDate + "'"
           + " AND (T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_ORDER + "'"           
           + " OR T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_PAY_ORDER + "')"
           + " AND V.HOSPITAL_CODE = '" + hospitalCode + "'" 
           + " AND T.INVOICE_DATE < '" + batch.getYyyy() + batch.getMm() + "00'" // difference
           + " AND (T.PAY_BY_CASH = 'N' AND T.PAY_BY_AR = 'N' AND T.PAY_BY_DOCTOR = 'N' "
           + " AND T.PAY_BY_PAYOR = 'N' AND T.PAY_BY_CASH_AR = 'N')";
        
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
//            DialogBox.ShowError("Error : " + ex.getMessage());
            System.out.println("importVerifyNotRec" + ex.getMessage());
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import verify data is fail.", ex.getMessage(), sql);
        } finally {
        	batch=null;
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    
    
    // DC 
    public boolean importDC(String hospitalCode, String startDate, String endDate) {
        boolean result = true;
//        UPDATE TRN_DAILY SET ACTIVE='0' 
//        	WHERE HOSPITAL_CODE = 'VTN01'
//        		AND HOSPITAL_CODE + INVOICE_NO + LINE_NO + DOCTOR_CODE = 
//        		(SELECT HOSPITAL_CODE + INVOICE_NO + ACCESSION_NUMBER + DOCTOR_CODE 
//        		FROM INT_HIS_DC
//        		WHERE HOSPITAL_CODE = 'VTN01' 
//        		AND (INVOICE_DATE >= '20091001' AND INVOICE_DATE <= '20091001')) 
        
        String sql = "UPDATE TRN_DAILY SET ACTIVE='0', IS_DC='Y' " +
             " WHERE HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND (HOSPITAL_CODE + INVOICE_NO + LINE_NO + DOCTOR_CODE) IN " + 
         	 " (SELECT HOSPITAL_CODE + INVOICE_NO + ACCESSION_NUMBER + DOCTOR_CODE " + 
         	 "    	FROM INT_HIS_DC " +
             " 		WHERE HOSPITAL_CODE = '" + hospitalCode + "'" +
             " 		AND (TRANSACTION_DATE >= '" + startDate + "' AND TRANSACTION_DATE <= '" + endDate + "'))";
//        HOSPITAL_CODE
//        DOCTOR_CODE
//        INVOICE_NO
//        INVOICE_DATE
//        MEDICAL_FEE
//        ACCESSION_NUMBER

        try {
//          conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("Import Bill : " + sql);
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import data error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    

    // Update STP_MAPPING_RULE
    public boolean updateStpMappingRule(String hospitalCode, String startDate, String endDate) {
        boolean result = true;

        String sql = "UPDATE TRN_DAILY SET TRN_DAILY.INVOICE_TYPE = " +
        	 " (SELECT S.INVOICE_TYPE FROM STP_MAPPING_RULE S WHERE S.DERIVED_FEE_RULE = TRN_DAILY.DERIVED_FEE_RULE) " +
             " 	WHERE HOSPITAL_CODE = '" + hospitalCode + "'" +
             " 		AND (TRANSACTION_DATE >= '" + startDate + "' AND TRANSACTION_DATE <= '" + endDate + "')";
        
        try {
//          conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("Update STP_MAPPING_RULE : " + sql);
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Update STP_MAPPING_RULE is error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    
    // Update PRIVATE_DOCTOR
//    UPDATE TRN_DAILY SET TRN_DAILY.PRIVATE_DOCTOR='Y' WHERE  LINE_NO IN (SELECT 
//    		 LINE_NO FROM INT_HIS_BILL  				WHERE PRIVATE_DOCTOR
//    		 +'/'=SUBSTRING(DOCTOR_CODE+'/',1,LEN(PRIVATE_DOCTOR)+1)  
//    		 AND (DOCTOR_CODE <> '')  				AND HOSPITAL_CODE = 'VTN01' 
//    		 AND (TRANSACTION_DATE >= '20100101' AND TRANSACTION_DATE <= '20100115')) AND 
//    		 HOSPITAL_CODE = 'VTN01' AND (TRANSACTION_DATE >= '20100101' AND TRANSACTION_DATE 
//    		 <= '20100115')
    public boolean updatePrivateDoctor_BILL(String hospitalCode, String startDate, String endDate) {
        boolean result = true;

        String sql = "UPDATE TRN_DAILY SET TRN_DAILY.PRIVATE_DOCTOR='Y' WHERE " +
        				" LINE_NO IN (SELECT LINE_NO FROM INT_HIS_BILL " + 
        				" 				WHERE PRIVATE_DOCTOR+'/'=SUBSTRING(DOCTOR_CODE+'/',1,LEN(PRIVATE_DOCTOR)+1) " +
        				" 				AND (DOCTOR_CODE <> '') " +
        	            " 				AND HOSPITAL_CODE = '" + hospitalCode + "'" +
        	            " 				AND (TRANSACTION_DATE >= '" + startDate + "' AND TRANSACTION_DATE <= '" + endDate + "'))" +
             " AND HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND (TRANSACTION_DATE >= '" + startDate + "' AND TRANSACTION_DATE <= '" + endDate + "')";
        
        try {
//          conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("Update Private Doctor : " + sql);
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Update PRIVATE_DOCTOR BILL is error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }        
    // Update PRIVATE_DOCTOR
//    SELECT T.LINE_NO,B.PRIVATE_DOCTOR, V.DOCTOR_CODE,* FROM TRN_DAILY T INNER JOIN INT_HIS_VERIFY V ON T.INVOICE_NO=V.BILL_NO
//    AND T.LINE_NO = V.LINE_NO+'V' INNER JOIN INT_HIS_BILL B ON V.BILL_NO=V.BILL_NO
//    AND B.LINE_NO=V.LINE_NO 
//    WHERE V.DOCTOR_CODE=B.PRIVATE_DOCTOR AND T.LINE_NO LIKE '%V'    
    public boolean updatePrivateDoctor_VERIFY(String hospitalCode, String startDate, String endDate) {
        boolean result = true;

        String sql = "UPDATE TRN_DAILY SET TRN_DAILY.PRIVATE_DOCTOR='Y' WHERE " +
        				" LINE_NO IN (SELECT V.LINE_NO+'V' FROM INT_HIS_VERIFY V " + 
        				" 				INNER JOIN INT_HIS_BILL B ON B.HOSPITAL_CODE = V.HOSPITAL_CODE " +
        				" 				AND B.LINE_NO=V.LINE_NO AND B.BILL_NO=V.BILL_NO " +
        				" 			WHERE B.PRIVATE_DOCTOR+'/'=SUBSTRING(V.DOCTOR_CODE+'/',1,LEN(B.PRIVATE_DOCTOR)+1)" +
        				"				AND (V.DOCTOR_CODE <> '') " +
        	            " 				AND V.HOSPITAL_CODE = '" + hospitalCode + "'" +
        	            " 				AND (V.TRANSACTION_DATE >= '" + startDate + "' AND V.TRANSACTION_DATE <= '" + endDate + "'))" +
             " AND HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND (TRANSACTION_DATE >= '" + startDate + "' AND TRANSACTION_DATE <= '" + endDate + "')";
        
        try {
//          conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("Update Private Doctor : " + sql);
            TRN_Error.writeErrorLog(this.conn, TRN_Error.PROCESS_IMPORT_BILL, 
                    "Update PRIVATE_DOCTOR VERIFY is error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }        
    
}
